package utils;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import common.CaseInfo;
import org.apache.commons.configuration.ConfigurationException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class ExcelUtils{

    public static final String system = "env.properties";


//    static {
//        try {
//            filePath = new GetFileMess().getValue("QC_EXCEL_PATH",system);
//        } catch (ConfigurationException e) {
//            e.printStackTrace();
//        }
//    }

        private String filePath;
    private int sheetNum = 0;

    public static List<WriteBackData> wdbList = new ArrayList<>();


    /**
     * 构造方法
     * */

    public ExcelUtils(String filePath,int sheetNum)  {
        this.filePath = filePath;
        this.sheetNum = sheetNum;
    }


    /**--------------------------------
     * 读取excel数据后封装到指定对象中 给测试用例使用
     *---------------------------------
     * @param sheetIndex 开始sheet索引
     * @param sheetNum   sheet个数
//     * @param StartRows   开始行
//     * @param ReadRows   读到第x行
//     * 例如从第1行开始读到第3行  StartRows:1 ,ReadRows:3;读了2行，读了第2，第3行
     * @param clazz      excel映射类字节对象
     * @return
     */

    public static List read(int sheetIndex, int sheetNum, Class clazz) {
        try {
            // 1. excel文件流
            String   filePath = new GetFileMess().getValue("QC_EXCEL_PATH",system);

//            FileInputStream fis = new FileInputStream(Contants.EXCEL_PATH);
            FileInputStream fis = new FileInputStream(filePath);
            // 2. easypoi 导入参数
            ImportParams params = new ImportParams();
            params.setStartSheetIndex(sheetIndex);//从第x个sheet开始读取
            params.setSheetNum(sheetNum);//读取x个sheet
//            params.setStartRows(StartRows);    //从x行开始读取
//            params.setReadRows(ReadRows);      //读到第x行

//            params.setNeedVerify(true);

            // 3. 导入
            List caseInfoList = ExcelImportUtil.importExcel(fis, clazz, params);
            // 4. 关流
            fis.close();
            return caseInfoList;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void batchWrite( String  filePath) throws Exception {
        //回写的逻辑：遍历wdbList集合，取出sheetIndex,rowNum,cellNum,content
//        FileInputStream fis = new FileInputStream(Contants.EXCEL_PATH);
//        String  filePath = new GetFileMess().getValue("QC_EXCEL_PATH",system);
        FileInputStream fis = new FileInputStream(filePath);
        Workbook sheets = WorkbookFactory.create(fis);
        for (WriteBackData wdb : wdbList) {
            int sheetIndex = wdb.getSheetIndex();
            int rowNum = wdb.getRowNum();
            int cellNum = wdb.getCellNum();
            String content = wdb.getContent();

            Sheet sheet = sheets.getSheetAt(sheetIndex);
            Row row = sheet.getRow(rowNum);
            Cell cell = row.getCell(cellNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            cell.setCellValue(content);
        }
//        FileOutputStream fos = new FileOutputStream(Contants.EXCEL_PATH);
        FileOutputStream fos = new FileOutputStream(filePath);
        sheets.write(fos);
        fis.close();
        fos.close();
    }


    /**
     *----------------------------------------
     * 读取excel 封装成集合（读取tianta筛选指标）
     * ---------------------------------------
     * 该程序需要传入一份excel 和excel的列数 行数由程序自动检测
     * 注意：该方法统计的行数是默认第一行为title 不纳入统计的
     * @param Indexname excel表头的指标值名称
     * @return
     */

    public    ArrayList<String> ReadExcel(String Indexname)  {

        // int column = 5;//column表示excel的列数

        ArrayList<String> list = new ArrayList<>();

        try {
            // 建需要读取的excel文件写入stream
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(filePath));
            // 指向sheet下标为0的sheet 即第一个sheet 也可以按在sheet的名称来寻找
            XSSFSheet sheet = workbook.getSheetAt(sheetNum);
            // 获取sheet1中的总行数
            int rowTotalCount = sheet.getLastRowNum();
            //获取总列数
            int columnCount = sheet.getRow(0).getPhysicalNumberOfCells();

//            System.out.println("行数为："+rowTotalCount+"列数为："+columnCount);


            /*------------------------------
             *获取excel表头有多少个分类的列数
             * ------------------------------
             * */
            int column = 0;
            XSSFRow row1 = sheet.getRow(0);
            for(int z =0; z <= columnCount-1; z++){
                String  cell = row1.getCell(z).toString();
                if(Objects.equals(cell.substring(cell.length()-2), "分类")){
                    column++;
                }else {
                    continue;
                }
            }
            /*------------------------------
             *获取excel表头第一个分类的所在列数
             * ------------------------------
             * */
            int first=0;
            for(int z =0; z <=columnCount-1; z++){
                String  cell = row1.getCell(z).toString();
                if(Objects.equals(cell.substring(cell.length()-2), "分类")){
                    first= z;
                    break;
                }
            }

            /*------------------------------
             *获取excel表头（指标值名称）所在列
             * ------------------------------
             * */
            int namecolumn=0;
            for(int z =0; z <= columnCount-1; z++){
                String  cell = row1.getCell(z).toString();
                if(Objects.equals(cell, Indexname)){
                    namecolumn= z;
                    break;
                }
            }

//            System.out.println(column+":"+first);
            /**
             * 循环读取excel的数据
             *
             */
            for (int i = 1; i <= rowTotalCount; i++) {
                // 获取第i行的row对象
                XSSFRow row = sheet.getRow(i);

                StringBuffer sb=new StringBuffer();
                //读取第一列的值，加到字符串里
                sb.append(row.getCell(0).toString());
                for (int j = first; j <= column+1; j++) {
                    //下列步骤为判断cell读取到的数据是否为null 并把不是null的加入到字符串里
//                    String cell = null;

//                     cell = row.getCell(j).toString();
                    if (row.getCell(j).toString()==""){
//                        break;

                    }else {
                        sb.append(","+row.getCell(j).toString());

                    }
                }
                sb.append(","+row.getCell(namecolumn).toString());

                //把一行行组成的数据加入到list里
                list.add(sb.toString());

            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void main(String[] args) {
        List<CaseInfo> list = read(0, 1, CaseInfo.class);
        for (CaseInfo caseInfo : list) {
          if (caseInfo.getTimeSlice().equals("半年")&& caseInfo.getMedCode().equals("COPD-001")) {
              System.out.println(caseInfo);
          }
        }



    }


}
